package my.home;

import java.sql.*;
import java.util.ArrayList;
import javax.sql.*;
import javax.naming.*;
import my.home.*;
import java.sql.Timestamp;

public class DBConnection{
	
	private static DBConnection instance = new DBConnection();
	
	public static DBConnection getInstance(){
		return instance;
	}
	
	private DBConnection(){}
	
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)envCtx.lookup("jdbc/mp");
		return ds.getConnection();
	}
	
	
	
	public ArrayList<String> getTimeData(String pollId) throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<String> timeInfo = new ArrayList<String>();
		
		try {
			conn = getConnection();
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT DATE_FORMAT(start_date, '%Y-%m-%d/%H:%i') as start_date, DATE_FORMAT(end_date, '%Y-%m-%d/%H:%i') as end_date FROM events order by start_date;");
			while (rs.next()) {
				String timeStr = rs.getString("start_date") + "/" + rs.getString("end_date"); 
				timeInfo.add(timeStr);
			}

			
		}catch(Exception ex){
			ex.printStackTrace();
			
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException ex){}
			if(stmt!=null)try{stmt.close();}catch(SQLException ex){}
			if(conn!=null)try{conn.close();}catch(SQLException ex){}
	}

		return timeInfo;
	}
	
	public boolean insertMember(meetingDataBean member) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("insert into meeting(M_title,M_password,M_location,M_description,M_initiatorAlias,M_initiatorEmail,Poll_time,Poll_id) values(?,?,?,?,?,?,?,?)");
			pstmt.setString(1, member.getM_title());
			pstmt.setString(2, member.getM_password());
			pstmt.setString(3, member.getM_location());
			pstmt.setString(4, member.getM_description());
			pstmt.setString(5, member.getM_initiatorAlias());
			pstmt.setString(6, member.getM_initiatorEmail());
			pstmt.setString(7, member.getPoll_time());
			pstmt.setString(8, member.getPoll_id());
			//pstmt.setTimestamp(9, member.getM_createtime());
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
				ex.printStackTrace();
				result = false;
		}finally{
				if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null)try{conn.close();}catch(SQLException ex){}
		}return result;
	}
}

	
